package com.ksv.lop;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.util.Log;

public class DBAdapter {

	public static final String TAG ="TTTH";
	
	private DatabaseHelper mDbHelper;
	private SQLiteDatabase mDB;
	
	static final String dbName = "qlsvDB";
	static final int dbVersion = 2;
	
	static public final String masv = "MaSV";
	static public final String hosv = "HoSV";
	static public final String tensv = "TenSV";
	static public final String hoten = "HoTen";
	static public final String gioitinh = "GioiTinh";
	static public final String diachi = "DiaChi";
	
	static public final String makhoa = "MaKhoa";
	static public final String tenkhoa = "TenKhoa";
	
	static public final String malop = "MaLop";
	static public final String tenlop = "TenLop";
	
	static public final String mamon = "MaMon";
	static public final String tenmon = "TenMon";
	
	static public final String mahk = "MaHK";
	static public final String tenhk = "TenHK";
	
	static public final String diem = "Diem";
	
	private final Context mContext;
	
	private static class DatabaseHelper extends SQLiteOpenHelper{

		public DatabaseHelper(Context context, String name,
				CursorFactory factory, int version) {
			super(context, name, factory, version);
			// TODO Auto-generated constructor stub
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			// TODO Auto-generated method stub
			try{
				Log.i(TAG, "CREATE TABLE BEGIN");
				
				db.execSQL("CREATE TABLE KHOA (MaKhoa INTEGER NOT NULL PRIMARY KEY, TenKhoa TEXT)");
				
				Log.i(TAG, "CREATE TABLE KHOA OK");
				
				db.execSQL("CREATE TABLE LOP (MaLop INTEGER NOT NULL PRIMARY KEY, TenLop TEXT, MaKhoa INTEGER  NOT NULL, FOREIGN KEY (MaKhoa) REFERENCES KHOA(MaKhoa))");
				
				Log.i(TAG, "CREATE TABLE LOP OK");
				
				db.execSQL("CREATE TABLE SINHVIEN (MaSV INTEGER NOT NULL PRIMARY KEY, HoSV TEXT NOT NULL, TenSV TEXT NOT NULL, GioiTinh INTEGER, DiaChi TEXT, MaLop INTEGER  NOT NULL, FOREIGN KEY (MaLop) REFERENCES LOP(MaLop))");
				
				Log.i(TAG, "CREATE TABLE SINHVIEN OK");
				
				db.execSQL("CREATE TABLE MONHOC (MaMon INTEGER  NOT NULL PRIMARY KEY, TenMon TEXT  NOT NULL)");
				
				Log.i(TAG, "CREATE TABLE MONHOC OK");
				
				db.execSQL("CREATE TABLE HOCKY (MaHK INTEGER  NOT NULL PRIMARY KEY, TenHK TEXT NOT NULL)");
				
				Log.i(TAG, "CREATE TABLE HOCKY OK");
				
				db.execSQL("CREATE TABLE KETQUA (MaSV INTEGER NOT NULL, MaMon INTEGER NOT NULL, MaHK INTEGER NOT NULL, Diem INTEGER NOT NULL, CONSTRAINT pk_ketqua_masv_mon_hk PRIMARY KEY(MaSV,MaMon,MaHK))");
				
				Log.i(TAG, "CREATE TABLE KETQUA OK");
				
				InsertKhoa(db);
				
				Log.i(TAG, "INSERT TABLE KHOA OK");
				
				InsertLop(db);
				
				Log.i(TAG, "INSERT TABLE LOP OK");
				
				InsertMonHoc(db);
				
				Log.i(TAG, "INSERT TABLE MONHOC OK");
				
				InsertHocKy(db);
				
				Log.i(TAG, "INSERT TABLE HOCKY OK");

			}
			catch(Exception ex){
				
			}
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			// TODO Auto-generated method stub

		}
		
		public void InsertKhoa(SQLiteDatabase db){
			ContentValues cv = new ContentValues();
			cv.put(makhoa, 1);
			cv.put(tenkhoa, "KHOA CONG NGHE THONG TIN");
			db.insert("KHOA", makhoa, cv);		
			cv.put(makhoa, 2);
			cv.put(tenkhoa, "KHOA CO DIEN TU");
			db.insert("KHOA", makhoa, cv);
			cv.put(makhoa, 3);
			cv.put(tenkhoa, "KHOA NGOAI NGU");
			db.insert("KHOA", makhoa, cv);
			cv.put(makhoa, 4);
			cv.put(tenkhoa, "KHOA CO KHI MAY");
			db.insert("KHOA", makhoa, cv);
			cv.put(makhoa, 5);
			cv.put(tenkhoa, "KHOA CONG NGHE MOI TRUONG");
			db.insert("KHOA", makhoa, cv);
		}
		
		private void InsertLop(SQLiteDatabase db) {
			// TODO Auto-generated method stub
			ContentValues cv = new ContentValues();
			cv.put(malop, 1);
			cv.put(tenlop, "051101");
			cv.put(makhoa, 1);
			db.insert("LOP", malop, cv);		
			cv.put(malop, 2);
			cv.put(tenlop, "051102");
			cv.put(makhoa, 1);
			db.insert("LOP", malop, cv);	
			cv.put(malop, 3);
			cv.put(tenlop, "051201");
			cv.put(makhoa, 2);
			db.insert("LOP", malop, cv);	
			cv.put(malop, 4);
			cv.put(tenlop, "051202");
			cv.put(makhoa, 2);
			db.insert("LOP", malop, cv);	
		}

		public void InsertMonHoc(SQLiteDatabase db){
			ContentValues cv = new ContentValues();
			cv.put(mamon, 1);
			cv.put(tenmon, "TOAN A1");	
			db.insert("MONHOC", mamon, cv);
			cv.put(mamon, 2);
			cv.put(tenmon, "TOAN A2");	
			db.insert("MONHOC", mamon, cv);
			cv.put(mamon, 3);
			cv.put(tenmon, "TOAN A3");	
			db.insert("MONHOC", mamon, cv);
			cv.put(mamon, 4);
			cv.put(tenmon, "LAP TRINH WEB");	
			db.insert("MONHOC", mamon, cv);
			cv.put(mamon, 5);
			cv.put(tenmon, "LAP TRINH JAVA");	
			db.insert("MONHOC", mamon, cv);
		}
		
		public void InsertHocKy(SQLiteDatabase db){
			ContentValues cv = new ContentValues();
			cv.put(mahk, 1);
			cv.put(tenhk, "HOC KY 1");	
			db.insert("HOCKY", mahk, cv);	
			cv.put(mahk, 2);
			cv.put(tenhk, "HOC KY 2");	
			db.insert("HOCKY", mahk, cv);
			cv.put(mahk, 3);
			cv.put(tenhk, "HOC KY 3");	
			db.insert("HOCKY", mahk, cv);
			cv.put(mahk, 4);
			cv.put(tenhk, "HOC KY 4");	
			db.insert("HOCKY", mahk, cv);
			cv.put(mahk, 5);
			cv.put(tenhk, "HOC KY 5");	
			db.insert("HOCKY", mahk, cv);
			cv.put(mahk, 6);
			cv.put(tenhk, "HOC KY 6");	
			db.insert("HOCKY", mahk, cv);
			cv.put(mahk, 7);
			cv.put(tenhk, "HOC KY 7");	
			db.insert("HOCKY", mahk, cv);
			cv.put(mahk, 8);
			cv.put(tenhk, "HOC KY 8");	
			db.insert("HOCKY", mahk, cv);
			cv.put(mahk, 9);
			cv.put(tenhk, "HOC KY 9");	
			db.insert("HOCKY", mahk, cv);
		 }
	}
	
	public DBAdapter(Context ctx){
		this.mContext = ctx;
	}
	
	public DBAdapter open()
	{
		mDbHelper = new DatabaseHelper(mContext, dbName, null, dbVersion);
		mDB = mDbHelper.getWritableDatabase();
		return this;
	}
	
	public void close(){
		mDbHelper.close();
	}
	
	public Cursor getAllMonHoc(){
		return mDB.rawQuery("SELECT * FROM MONHOC ORDER BY TENMON ASC", null);
	}
	
	public Cursor getAllMonHoc_Search(String search){
		return mDB.rawQuery("SELECT MAMON AS _id,* FROM MONHOC WHERE TENMON LIKE '"+ search +"%'", null);
	}
	
	public long AddMonHoc(MonHoc mh){
		ContentValues cv = new ContentValues();
		cv.put(mamon, mh.getMaMon());
		cv.put(tenmon, mh.getTenMon());	
		long kq = mDB.insert("MONHOC", mamon, cv);
		return kq;
	}
	
	public int updateMonHoc(MonHoc mh){
		int kq = -1;
		ContentValues cv = new ContentValues();
		cv.put(tenmon, mh.getTenMon());
		kq = mDB.update("MONHOC", cv, mamon + "=?", new String []{String.valueOf(mh.getMaMon())});
		return kq;
	}

}
